package com.yunji.news.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.yunji.news.entity.ScoreLine;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 分数线Mapper接口
 *
 * @author yunji
 * @since 2024-11-25
 */
@Mapper
public interface ScoreLineMapper extends BaseMapper<ScoreLine> {

    /**
     * 根据院校ID查询分数线列表
     *
     * @param schoolId 院校ID
     * @return 分数线列表
     */
    @Select("SELECT * FROM score_lines WHERE school_id = #{schoolId} AND status = 1 AND deleted = 0 ORDER BY year DESC, batch")
    List<ScoreLine> selectBySchoolId(@Param("schoolId") Long schoolId);

    /**
     * 根据专业ID查询分数线列表
     *
     * @param majorId 专业ID
     * @return 分数线列表
     */
    @Select("SELECT * FROM score_lines WHERE major_id = #{majorId} AND status = 1 AND deleted = 0 ORDER BY year DESC")
    List<ScoreLine> selectByMajorId(@Param("majorId") Long majorId);

    /**
     * 查询最近几年的分数线数据
     *
     * @param years 年数
     * @return 分数线列表
     */
    @Select("SELECT * FROM score_lines WHERE year >= (YEAR(NOW()) - #{years}) AND status = 1 AND deleted = 0 ORDER BY year DESC, school_id, major_id")
    List<ScoreLine> selectRecentYears(@Param("years") Integer years);

    /**
     * 根据年份查询分数线列表
     *
     * @param year 年份
     * @return 分数线列表
     */
    @Select("SELECT * FROM score_lines WHERE year = #{year} AND status = 1 AND deleted = 0 ORDER BY min_score DESC")
    List<ScoreLine> selectByYear(@Param("year") Integer year);

    /**
     * 查询所有年份
     *
     * @return 年份列表
     */
    @Select("SELECT DISTINCT year FROM score_lines WHERE status = 1 AND deleted = 0 ORDER BY year DESC")
    List<Integer> selectYears();

    /**
     * 查询所有批次
     *
     * @return 批次列表
     */
    @Select("SELECT DISTINCT batch FROM score_lines WHERE status = 1 AND deleted = 0 AND batch IS NOT NULL ORDER BY batch")
    List<String> selectBatches();

    /**
     * 查询所有科目类型
     *
     * @return 科目类型列表
     */
    @Select("SELECT DISTINCT subject_type FROM score_lines WHERE status = 1 AND deleted = 0 AND subject_type IS NOT NULL ORDER BY subject_type")
    List<String> selectSubjectTypes();
}